package cn.dida.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.ArrayUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import cn.dida.model.ActivityWinning;
import cn.dida.model.DeliveryInfo;
import cn.dida.model.Pageable;
import cn.dida.model.Prize;
import cn.dida.model.Product;
import org.springframework.transaction.annotation.Transactional;

/**
 * <p> 兑奖记录  数据交互类</p>
 * @author lisha
 * @date 2016-11-20 18:22
 * */
@Repository
public class ActivityWinningDao extends DaoBase {
	/**
	 * 根据id获取兑奖记录详情
	 * @param id 兑奖记录id
	 * @return ActivityWinning 兑奖记录详情
	 * @date 2016-11-20 19:50
	 * */
    public ActivityWinning getWinningById(String id) {
    	String sql = "SELECT aw.cssh_mobile,aw.activity_prize_id,aw.activity_id,aw.qrcode_id,aw.qrcode_type,aw.qrcode_name, aw.id,aw.cash_time,aw.mobile,aw.scan_time,"
        		+ "p.`name` 'productName',p.number 'productNumber',r.name 'prizeName',r.type 'prizeType',aw.poi_address 'poiAddress',"
        		+ "r.price 'prizePrice', r.shipping_method 'shipMethod',aw.consignee,aw.mobile,aw.adress,log.issuccess 'redEnvIsSuc', log.err_des 'redEnvErrorDes'" +
				" from activity_winning aw LEFT JOIN product p ON aw.product_id = p.id" +
				" LEFT JOIN prize r on aw.prize_id = r.id" +
				" LEFT JOIN activity_winning_sendred_log log on log.actvity_winning_id = aw.id " +
				" where aw.id=?";
    	
    	List<ActivityWinning> result = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ActivityWinning.class),id);
    	return result.get(0);
    }

	/**
	 * 点击发送: 修改获奖列表，修改红包获奖日志表， 修改奖品库存
	 * @param winningId
	 * @param prizeId
	 * @return
	 */
    @Transactional
    public Integer sendEnvelope(Integer winningId, Integer prizeId,Integer issuccess){
    	//activity_winning_sendred_log
		Long time = new Date().getTime();
		String logSql = "" ;
		if(issuccess == 0){
			logSql ="update activity_winning_sendred_log set issuccess = 0,c_time=" + time+" where actvity_winning_id ="+ winningId;
		}else {
			logSql ="update activity_winning_sendred_log set issuccess = 2,c_time=" + time+" where actvity_winning_id ="+ winningId;
		}
    	//activity_winning
		String winSql = "update activity_winning set cash_time="+time + ",issuccess = "+issuccess+" where id="+winningId;
		String prizeSql = "update prize set stock = stock-1 where id="+prizeId;

		 jdbcTemplate.update(logSql);
		 jdbcTemplate.update(prizeSql);
		 return jdbcTemplate.update(winSql);

	}

	/**
	 * 获取发送失败的红包数据
	 * @param companyId
	 * @return
	 */
	public List<Map<String,Object>> getSendEvnFalse(Integer companyId,String activityId){

    	String sql = "select log.openid, log.amount from activity_winning_sendred_log log join activity_winning ac" +
				" on ac.id=log.actvity_winning_id join activity act on act.id= ac.activity_id where log.issuccess=0 and act.company_id=?";
		if(StringUtils.isNotBlank(activityId)){
			sql += " and ac.activity_id =" +activityId ;
		}
		List<Map<String,Object>> result = jdbcTemplate.queryForList(sql,companyId);
    	return result;
	}


	/**
	 * 	获取公司uuid
	 */
	public String getCompanyPreference(Integer companyId){
		String sql = "select preference from company where id=?";
		List<Map<String,Object>> list = jdbcTemplate.queryForList(sql, companyId);
		if (null != list && list.size() == 1) {
			return (String)list.get(0).get("preference");
		}
		return null;
	}


    /**
     * 兑奖列表
     * @param qrcodeNum 二维码编号 
     * @param qrcodeName 二维码名称
     * @param productName 商品名称
     * @param prizeName 奖品名称
     * @param csshMobile 兑奖手机号
     * @param cashTimeS 兑奖时间开始
     * @param cashTimeE 兑奖时间结束
     * @param scanTimeS 扫码时间开始
     * @param scanTimeE 扫码时间结束
     * @param page 分页游标
	 * @return Pageable<ActivityWinning> 兑奖管理分页列表
	 * @date 2016-11-20 19:43
     * */
    public Pageable<ActivityWinning> query(Integer companyId,String qrcodeNum, String qrcodeName,String productName,String prizeName,
    		String csshMobile,String cashTimeS,String cashTimeE,String scanTimeS,String scanTimeE,Integer page,Integer shipMethod,
    		String activityId,Integer redEnvelope) {
    	 StringBuffer sqlWhere  = new StringBuffer();
        Object[] args = {};
        StringBuffer sqlQueryResultCount = new StringBuffer("SELECT COUNT(1) from activity_winning aw LEFT JOIN product p "
        		+ "ON aw.product_id = p.id LEFT JOIN prize r on aw.prize_id = r.id left join activity ac "
        		+ "on aw.activity_id = ac.id  LEFT JOIN activity_winning_sendred_log log on log.actvity_winning_id = aw.id"
        		+ " where 1=1 and ac.company_id = ? ");
        StringBuffer sqlQueryResult = new StringBuffer("SELECT aw.product_id,aw.prize_id,aw.cssh_mobile,aw.qrcode_id,aw.qrcode_type,aw.qrcode_name, aw.id, aw.cash_time,aw.mobile,aw.scan_time,"
        		+ "p.`name` 'productName',r.name 'prizeName',r.id 'prizeId',log.issuccess 'redEnvIsSuc',log.err_des 'redEnvErrorDes', aw.poi_city 'poiCity',aw.poi_address 'poiAddress',"
        		+"case when(select count(*) from delivery_info where activity_winning_id = aw.id) >0 then 1 else 0 end as 'haveDeliveryInfo',"
        		+ "r.price 'prizePrice', r.shipping_method 'shipMethod' from activity_winning aw LEFT JOIN product p"
        		+ " ON aw.product_id = p.id LEFT JOIN prize r on aw.prize_id = r.id left join activity ac on aw.activity_id = ac.id "
				+" LEFT JOIN activity_winning_sendred_log log on log.actvity_winning_id = aw.id"
        		+ " where 1=1 and ac.company_id = ? ");

        args = ArrayUtils.add(args, companyId);
        //查询条件：商品名称
        if(StringUtils.isNotBlank(productName)){
        	String sql = "select id from product where name like ?";
        	Object[] args_pro = {};
        	args_pro = ArrayUtils.add(args_pro, "%" + productName + "%");
        	 List<Product> result = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(Product.class),args_pro);
        	 if(result.size() > 0){
        		 sqlWhere.append(" and (");
        		 for(int i =0; i< result.size();i++){
        			sqlWhere.append(" aw.product_id = ? || ");
          			args = ArrayUtils.add(args, result.get(i).getId());
          			if(i == result.size()-1){//最后一个product_id
          				sqlWhere.append(" aw.product_id = ? )");
              			args = ArrayUtils.add(args, result.get(i).getId());
          			}
        		 }
        	 }else{
        		 sqlWhere.append("and aw.product_id = -1 ");//-1表示查询不到任何内容
        	 }
        }
        //查询条件：奖品名称
        if(StringUtils.isNotBlank(prizeName)){
        	String sql = "select id from prize where name like ?";
        	Object[] args_prize = {};
        	args_prize = ArrayUtils.add(args_prize, "%" + prizeName + "%");
        	List<Prize> result = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(Prize.class),args_prize);
        	if(result.size() > 0){
        		sqlWhere.append(" and (");
        		for(int i = 0; i < result.size(); i++){
        			sqlWhere.append(" aw.prize_id = ? || ");
        			args = ArrayUtils.add(args, result.get(i).getId());
        			if(i == result.size()-1){//最后一个prizeid
        				sqlWhere.append(" aw.prize_id = ? )");
            			args = ArrayUtils.add(args, result.get(i).getId());
        			}
        		}
        	}else{
        		sqlWhere.append("and aw.prize_id = -1 ");//-1表示查询不到任何内容
        	}        	
        }
        
        if(StringUtils.isNotBlank(activityId)){
        	sqlWhere.append("and ac.id = ?");
        	args = ArrayUtils.add(args, activityId);
        }
        if(StringUtils.isNotBlank(qrcodeNum)){
        	sqlWhere.append("and aw.qrcode_id like ?");
        	args = ArrayUtils.add(args, "%"+qrcodeNum+"%");
        }
        if(StringUtils.isNotBlank(qrcodeName)){
        	sqlWhere.append("and aw.qrcode_name like ?");
        	args = ArrayUtils.add(args, "%"+qrcodeName+"%");
        	
        }
        if(StringUtils.isNotBlank(csshMobile)){
        	sqlWhere.append(" and aw.cssh_mobile = ? ");
        	args = ArrayUtils.add(args, csshMobile);
        }
        
        //扫码时间不为空
        if(StringUtils.isNotBlank(scanTimeS) && StringUtils.isNotBlank(scanTimeE)){
        	sqlWhere.append(" and (scan_time >= ? and  scan_time <= ?)");
        	long scans = Long.parseLong(scanTimeS);
        	long scane = Long.parseLong(scanTimeE);
        	args = ArrayUtils.add(args, scans);
        	args = ArrayUtils.add(args, scane);
        }else if(StringUtils.isNotBlank(scanTimeS)){
        	sqlWhere.append(" and scan_time >= ? ");
        	long scans = Long.parseLong(scanTimeS);
        	args = ArrayUtils.add(args, scans);
        }else if(StringUtils.isNotBlank(scanTimeE)){
        	sqlWhere.append(" and scan_time <= ? ");
        	long scane = Long.parseLong(scanTimeE);
        	args = ArrayUtils.add(args, scane);
        }
        //兑奖时间
        if(StringUtils.isNotBlank(cashTimeS) && StringUtils.isNotBlank(cashTimeE)){
        	sqlWhere.append(" and (cash_time >= ? and cash_time<= ?)");
        	long cash_s = Long.parseLong(cashTimeS);
        	long cash_e = Long.parseLong(cashTimeE);
        	args = ArrayUtils.add(args, cash_s);
        	args = ArrayUtils.add(args, cash_e);
        }else if(StringUtils.isNotBlank(cashTimeS)){
        	sqlWhere.append(" and cash_time >= ? ");
        	long cash_s = Long.parseLong(cashTimeS);
        	args = ArrayUtils.add(args, cash_s);
        }else if(StringUtils.isNotBlank(cashTimeE)){
        	sqlWhere.append(" and cash_time <= ? ");
        	long cash_e = Long.parseLong(cashTimeE);
        	args = ArrayUtils.add(args, cash_e);
        }
        if(shipMethod != null){
        	sqlWhere.append(" and r.shipping_method = ? ");
        	args = ArrayUtils.add(args, shipMethod);
        }
		if(redEnvelope != null){
			sqlWhere.append(" and log.issuccess = ? ");
			args = ArrayUtils.add(args, redEnvelope);
		}
        
        // 查询记录总数
        Integer resultTotal = jdbcTemplate.queryForObject(sqlQueryResultCount.append(sqlWhere).toString(), Integer.class, args);
        
        // 查询记录
        sqlQueryResult.append(sqlWhere).append("  order by aw.scan_time desc limit ?,?");
        args = ArrayUtils.add(args, (page - 1) * getPageSize());
        args = ArrayUtils.add(args, getPageSize());
        List<ActivityWinning> result = jdbcTemplate.query(sqlQueryResult.toString(), args, new BeanPropertyRowMapper<>(ActivityWinning.class));
        
        return new Pageable<>(page, getPageSize(), resultTotal, result);
    }
    /**
     * 兑奖列表(数据导出用)
     * @param companyId 公司id
     * @param qrcodeNum 二维码编号 
     * @param qrcodeName 二维码名称
     * @param productName 商品名称
     * @param prizeName 奖品名称
     * @param csshMobile 兑奖手机号
     * @param cashTimeS 兑奖时间开始
     * @param cashTimeE 兑奖时间结束
     * @param scanTimeS 扫码时间开始
     * @param scanTimeE 扫码时间结束
     * @return List<ActivityWinning> 兑奖管理列表
     * @date 2016-12-07 17:49
     * */
    public List<ActivityWinning> queryForExport(Integer companyId,String activityId,String qrcodeNum, String qrcodeName,String productName,String prizeName,
    		String csshMobile,String cashTimeS,String cashTimeE,String scanTimeS,String scanTimeE, Integer shipMethod,Integer redEnvelope) {
    	StringBuffer sqlWhere  = new StringBuffer();
    	Object[] args = {};
		StringBuffer sqlQueryResult = new StringBuffer("SELECT aw.product_id,aw.prize_id,aw.cssh_mobile 'csshMobile',aw.qrcode_id,aw.qrcode_type,aw.qrcode_name, " +
				"aw.id, aw.cash_time,aw.scan_time,aw.weixin_openid 'weixinOpenid',aw.consignee,aw.mobile,aw.adress,"
    			+ "p.`name` 'productName',r.name 'prizeName',r.id 'prizeId',log.issuccess 'redEnvIsSuc',log.err_des 'redEnvErrorDes', aw.poi_city 'poiCity',aw.poi_address 'poiAddress',"
    			+"case when(select count(*) from delivery_info where activity_winning_id = aw.id) >0 then 1 else 0 end as 'haveDeliveryInfo',"
    			+ "r.price 'prizePrice', r.shipping_method 'shipMethod' from activity_winning aw LEFT JOIN product p"
    			+ " ON aw.product_id = p.id LEFT JOIN prize r on aw.prize_id = r.id left join activity ac on aw.activity_id = ac.id "
				+" LEFT JOIN activity_winning_sendred_log log on log.actvity_winning_id = aw.id"
    			+ " where 1=1 and ac.company_id = ? ");
    	args = ArrayUtils.add(args, companyId);
		//查询条件：商品名称
		if(StringUtils.isNotBlank(productName)){
			String sql = "select id from product where name like ?";
			Object[] args_pro = {};
			args_pro = ArrayUtils.add(args_pro, "%" + productName + "%");
			List<Product> result = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(Product.class),args_pro);
			if(result.size() > 0){
				sqlWhere.append(" and (");
				for(int i =0; i< result.size();i++){
					sqlWhere.append(" aw.product_id = ? || ");
					args = ArrayUtils.add(args, result.get(i).getId());
					if(i == result.size()-1){//最后一个product_id
						sqlWhere.append(" aw.product_id = ? )");
						args = ArrayUtils.add(args, result.get(i).getId());
					}
				}
			}else{
				sqlWhere.append("and aw.product_id = -1 ");//-1表示查询不到任何内容
			}
		}
		//查询条件：奖品名称
		if(StringUtils.isNotBlank(prizeName)){
			String sql = "select id from prize where name like ?";
			Object[] args_prize = {};
			args_prize = ArrayUtils.add(args_prize, "%" + prizeName + "%");
			List<Prize> result = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(Prize.class),args_prize);
			if(result.size() > 0){
				sqlWhere.append(" and (");
				for(int i = 0; i < result.size(); i++){
					sqlWhere.append(" aw.prize_id = ? || ");
					args = ArrayUtils.add(args, result.get(i).getId());
					if(i == result.size()-1){//最后一个prizeid
						sqlWhere.append(" aw.prize_id = ? )");
						args = ArrayUtils.add(args, result.get(i).getId());
					}
				}
			}else{
				sqlWhere.append("and aw.prize_id = -1 ");//-1表示查询不到任何内容
			}
		}

		if(StringUtils.isNotBlank(activityId)){
			sqlWhere.append("and ac.id = ?");
			args = ArrayUtils.add(args, activityId);
		}
		if(StringUtils.isNotBlank(qrcodeNum)){
			sqlWhere.append("and aw.qrcode_id like ?");
			args = ArrayUtils.add(args, "%"+qrcodeNum+"%");
		}
		if(StringUtils.isNotBlank(qrcodeName)){
			sqlWhere.append("and aw.qrcode_name like ?");
			args = ArrayUtils.add(args, "%"+qrcodeName+"%");

		}
		if(StringUtils.isNotBlank(csshMobile)){
			sqlWhere.append(" and aw.cssh_mobile = ? ");
			args = ArrayUtils.add(args, csshMobile);
		}

		//扫码时间不为空
		if(StringUtils.isNotBlank(scanTimeS) && StringUtils.isNotBlank(scanTimeE)){
			sqlWhere.append(" and (scan_time >= ? and  scan_time <= ?)");
			long scans = Long.parseLong(scanTimeS);
			long scane = Long.parseLong(scanTimeE);
			args = ArrayUtils.add(args, scans);
			args = ArrayUtils.add(args, scane);
		}else if(StringUtils.isNotBlank(scanTimeS)){
			sqlWhere.append(" and scan_time >= ? ");
			long scans = Long.parseLong(scanTimeS);
			args = ArrayUtils.add(args, scans);
		}else if(StringUtils.isNotBlank(scanTimeE)){
			sqlWhere.append(" and scan_time <= ? ");
			long scane = Long.parseLong(scanTimeE);
			args = ArrayUtils.add(args, scane);
		}
		//兑奖时间
		if(StringUtils.isNotBlank(cashTimeS) && StringUtils.isNotBlank(cashTimeE)){
			sqlWhere.append(" and (cash_time >= ? and cash_time<= ?)");
			long cash_s = Long.parseLong(cashTimeS);
			long cash_e = Long.parseLong(cashTimeE);
			args = ArrayUtils.add(args, cash_s);
			args = ArrayUtils.add(args, cash_e);
		}else if(StringUtils.isNotBlank(cashTimeS)){
			sqlWhere.append(" and cash_time >= ? ");
			long cash_s = Long.parseLong(cashTimeS);
			args = ArrayUtils.add(args, cash_s);
		}else if(StringUtils.isNotBlank(cashTimeE)){
			sqlWhere.append(" and cash_time <= ? ");
			long cash_e = Long.parseLong(cashTimeE);
			args = ArrayUtils.add(args, cash_e);
		}
		if(shipMethod != null){
			sqlWhere.append(" and r.shipping_method = ? ");
			args = ArrayUtils.add(args, shipMethod);
		}
		if(redEnvelope != null){
			sqlWhere.append(" and log.issuccess = ? ");
			args = ArrayUtils.add(args, redEnvelope);
		}

		// 查询记录
		sqlQueryResult.append(sqlWhere).append("  order by aw.scan_time desc");

		List<ActivityWinning> result = jdbcTemplate.query(sqlQueryResult.toString(), args, new BeanPropertyRowMapper<>(ActivityWinning.class));

		return result;
    }
    /**
     * 根据兑奖记录id获取配送信息
     * @param winningId 兑奖记录id
     * @return DeliveryInfo 配送信息
     * @date 2016-12-01 11:19
     * */
    public DeliveryInfo getDeliveryInfo(String winningId){
    	String sql="select info.*,lo.name 'logisticsName',lo.website 'logisticsWebsite' from delivery_info info left join "
    			+ "logistics lo on info.logistics_company_id = lo.id "
    			+ "where info.activity_winning_id =?";
    	List<DeliveryInfo> result = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(DeliveryInfo.class),winningId);
    	if(result.size() == 0){
    		return new DeliveryInfo();
    	}else{
    		return result.get(0);
    	}
    }
    /**
   	 * 根据活动id获取兑奖列表(活动列表中【兑奖列表】使用)
   	 * @param page 分页游标
   	 * @param activityId 活动id
   	 * @return Pageable<ActivityPrize> 中奖规则分页列表
   	 * @date 2016-11-23 11:54
   	 * */
   	 public Pageable<ActivityWinning> getActivityWinningList(String activityId,Integer companyId,String qrcodeNum, String qrcodeName,String productName,String prizeName,
   	    		String mobile,String cashTimeS,String cashTimeE,String scanTimeS,String scanTimeE,Integer page,Integer shipMethod) {
   	    	 StringBuffer sqlWhere  = new StringBuffer();
   	        Object[] args = {};
   	        StringBuffer sqlQueryResultCount = new StringBuffer("SELECT COUNT(1) from activity_winning aw LEFT JOIN product p "
   	        		+ "ON aw.product_id = p.id LEFT JOIN prize r on aw.prize_id = r.id left join activity ac "
   	        		+ "on aw.activity_id = ac.id "
   	        		+ " where 1=1 and ac.company_id = ? and ac.id=? ");
   	        StringBuffer sqlQueryResult = new StringBuffer("SELECT aw.product_id,aw.prize_id,aw.cssh_mobile,aw.qrcode_id,aw.qrcode_type,aw.qrcode_name, aw.id, aw.cash_time,aw.mobile,aw.scan_time,"
   	        		+ "p.`name` 'productName',r.name 'prizeName', aw.poi_city 'poiCity',"
   	        		+"case when(select count(*) from delivery_info where activity_winning_id = aw.id) >0 then 1 else 0 end as 'haveDeliveryInfo',"
   	        		+ "r.price 'prizePrice', r.shipping_method 'shipMethod' from activity_winning aw LEFT JOIN product p"
   	        		+ " ON aw.product_id = p.id LEFT JOIN prize r on aw.prize_id = r.id left join activity ac on aw.activity_id = ac.id "
   	        		+ " where 1=1 and ac.company_id = ? and ac.id=? ");

   	        args = ArrayUtils.add(args, companyId);
   	        args = ArrayUtils.add(args, activityId);
   	        //查询条件：商品名称
   	        if(StringUtils.isNotBlank(productName)){
   	        	String sql = "select id from product where name like ?";
   	        	Object[] args_pro = {};
   	        	args_pro = ArrayUtils.add(args_pro, "%" + productName + "%");
   	        	List<Product> result = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(Product.class),args_pro);
   	        	if(result.size() > 0){
   	        		
   	       		 	sqlWhere.append(" and (");
	   	       		 for(int i =0; i< result.size();i++){
	   	       			sqlWhere.append(" aw.product_id = ? || ");
   	         			args = ArrayUtils.add(args, result.get(i).getId());
   	         			if(i == result.size()-1){//最后一个product_id
   	         				sqlWhere.append(" aw.product_id = ? )");
   	             			args = ArrayUtils.add(args, result.get(i).getId());
   	         			}
	   	       		 }
   		       	 }else{
   		       		 sqlWhere.append("and aw.product_id = -1 ");//-1表示查询不到任何内容
   		       	 }
   	        }
   	        //查询条件：奖品名称
   	        if(StringUtils.isNotBlank(prizeName)){
   	        	String sql = "select id from prize where name like ?";
   	        	Object[] args_prize = {};
   	        	args_prize = ArrayUtils.add(args_prize, "%" + prizeName + "%");
   	        	List<Prize> result = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(Prize.class),args_prize);
   	        	if(result.size() > 0){
   	        		sqlWhere.append(" and (");
   	        		for(int i = 0; i < result.size(); i++){
   	        			sqlWhere.append(" aw.prize_id = ? || ");
   	        			args = ArrayUtils.add(args, result.get(i).getId());
   	        			if(i == result.size()-1){//最后一个prizeid
   	        				sqlWhere.append(" aw.prize_id = ? )");
   	            			args = ArrayUtils.add(args, result.get(i).getId());
   	        			}
   	        		}
   	        	}else{
   	        		sqlWhere.append("and aw.prize_id = -1 ");//-1表示查询不到任何内容
   	        	}
   	        }
   	        
   	        if(StringUtils.isNotBlank(qrcodeNum)){
   	        	sqlWhere.append("and aw.qrcode_id like ?");
   	        	args = ArrayUtils.add(args, "%"+qrcodeNum+"%");
   	        }
   	        if(StringUtils.isNotBlank(qrcodeName)){
   	        	sqlWhere.append("and aw.qrcode_name like ?");
   	        	args = ArrayUtils.add(args, "%"+qrcodeName+"%");
   	        	
   	        }
   	        if(StringUtils.isNotBlank(mobile)){
   	        	sqlWhere.append(" and cssh_mobile = ? ");
   	        	args = ArrayUtils.add(args, mobile);
   	        }
   	        
   	        //扫码时间不为空
   	        if(StringUtils.isNotBlank(scanTimeS) && StringUtils.isNotBlank(scanTimeE)){
   	        	sqlWhere.append(" and (scan_time between ? and ?)");
   	        	long scans = Long.parseLong(scanTimeS);
   	        	long scane = Long.parseLong(scanTimeE);
   	        	args = ArrayUtils.add(args, scans);
   	        	args = ArrayUtils.add(args, scane);
   	        }else if(StringUtils.isNotBlank(scanTimeS)){
   	        	sqlWhere.append(" and scan_time >= ? ");
   	        	long scans = Long.parseLong(scanTimeS);
   	        	args = ArrayUtils.add(args, scans);
   	        }else if(StringUtils.isNotBlank(scanTimeE)){
   	        	sqlWhere.append(" and scan_time <= ? ");
   	        	long scane = Long.parseLong(scanTimeE);
   	        	args = ArrayUtils.add(args, scane);
   	        }
   	        //兑奖时间
   	        if(StringUtils.isNotBlank(cashTimeS) && StringUtils.isNotBlank(cashTimeE)){
   	        	sqlWhere.append(" and (cash_time between ? and ?)");
   	        	long cash_s = Long.parseLong(cashTimeS);
   	        	long cash_e = Long.parseLong(cashTimeE);
   	        	args = ArrayUtils.add(args, cash_s);
   	        	args = ArrayUtils.add(args, cash_e);
   	        }else if(StringUtils.isNotBlank(cashTimeS)){
   	        	sqlWhere.append(" and cash_time >= ? ");
   	        	long cash_s = Long.parseLong(cashTimeS);
   	        	args = ArrayUtils.add(args, cash_s);
   	        }else if(StringUtils.isNotBlank(cashTimeE)){
   	        	sqlWhere.append(" and cash_time <= ? ");
   	        	long cash_e = Long.parseLong(cashTimeE);
   	        	args = ArrayUtils.add(args, cash_e);
   	        }
   	        
	   	     if(shipMethod != null){
	         	sqlWhere.append(" and r.shipping_method = ? ");
	         	args = ArrayUtils.add(args, shipMethod);
	         }
   	        // 查询记录总数
   	        Integer resultTotal = jdbcTemplate.queryForObject(sqlQueryResultCount.append(sqlWhere).toString(), Integer.class, args);
   	        
   	        // 查询记录
   	        sqlQueryResult.append(sqlWhere).append("  order by aw.scan_time desc limit ?,?");
   	        args = ArrayUtils.add(args, (page - 1) * getPageSize());
   	        args = ArrayUtils.add(args, getPageSize());
   	        List<ActivityWinning> result = jdbcTemplate.query(sqlQueryResult.toString(), args, new BeanPropertyRowMapper<>(ActivityWinning.class));
   	        
   	        return new Pageable<>(page, getPageSize(), resultTotal, result);
   	    }
   	 
	   	/**
	   	 * 获取奖品内容
	   	 * @author kelan
	   	 * @date 2017年5月9日
	   	 * @param prizeid
	   	 * @param awinid
	   	 * @return
	   	 */
	   	public Map<String, Object> queryPrizeInfoMapById(Integer prizeid, Integer awinid) {
	
			String sql = "SELECT aw.weixin_openid,aw.id,aw.cash_time ,aw.mobile,ap.grade_name,p.`name` AS pname,p.type,p.price,p.image,a.`name` actname,ap.is_attention FROM prize p,activity_winning aw,activity_prize ap ,activity a "
					+ " WHERE p.id = ap.prize_id AND ap.id = aw.activity_prize_id AND aw.activity_id = a.id  AND p.id = ?  AND aw.id = ?";
	
			List<Map<String, Object>> list = jdbcTemplate.queryForList(sql, prizeid, awinid);
			if (null != list && list.size() == 1) {
				return list.get(0);
			}
			return null;
		}
	   	
	   	
	    //删除同一中奖下的红包发送日志
		public Integer deleteActivity_winning_sendred_log(Integer actwinid) {
			String sql = "delete from activity_winning_sendred_log  where actvity_winning_id = ?  ";
			return jdbcTemplate.update(sql, actwinid);
		}
		
		public Long insertActivity_winning_sendred_log(Integer actwinid, Long time,Integer source,String openid,Double price,String message_id) {
			// TODO Auto-generated method stub
			String sql = "insert into activity_winning_sendred_log (actvity_winning_id,c_time,source,openid,issuccess,amount,message_id) values (?,?,?,?,?,?,?)";

			KeyHolder keyHolder = new GeneratedKeyHolder();
			jdbcTemplate.update(new PreparedStatementCreator() {

				@Override
				public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {

					PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
					ps.setInt(1, actwinid);
					ps.setLong(2, time);
					ps.setInt(3, source);
					ps.setString(4, openid);
					ps.setInt(5, 3);//默认正在发送中
					ps.setDouble(6, price);
					ps.setString(7, message_id);
					return ps;
				}
			}, keyHolder);

			long generatedId = keyHolder.getKey().longValue();
			return generatedId;
		}
		
		public  Map<String, Object>  queryMessage_Activity_winning_ID(String message_id) {
			// TODO Auto-generated method stub
			String sql = "select log.id,log.actvity_winning_id,w.activity_prize_id "
						 +" from activity_winning_sendred_log log "
						 +" join activity_winning w on log.actvity_winning_id=w.id"
						 +" where log.message_id=?  ";
			return jdbcTemplate.queryForMap(sql, message_id);
		}
		
		public Integer calbackupdateActivitywinningsendredlog(String messageid, Integer issuccess, String err_code_des) {
			// TODO Auto-generated method stub
			String sql = "update activity_winning_sendred_log set issuccess =?,err_des = ? where message_id = ?  ";

			return jdbcTemplate.update(sql, issuccess, err_code_des, messageid);
		}


	/**
	 * 统计报表数据
	 * @param companyId
	 * @param activityId
	 * @return
	 */
	public List<Map<String,Object>> statisticalFormList(Integer companyId,String activityId) {
		StringBuffer sqlWhere  = new StringBuffer();
		Object[] args = {};
		StringBuffer sqlQueryResult = new StringBuffer("select pro.id, ac.id,pro.number 'proNumber',pro.name 'proName', ac.name 'actName'," +
				"ac.act_time_start 'actTimeSta',ac.act_time_end 'actTimeEnd'," +
				"aw.prize_id, if(aw.prize_id=0,'未中奖',pri.name) 'priName',count(*) 'priCount'," +
				"(select count(*) from activity_winning aw1 where aw1.product_id= aw.product_id and aw1.activity_id=aw.activity_id " +
				" and aw1.cash_time is null and  aw1.prize_id!=0) 'noAward'," +  //未领奖数
				"(select count(*) from activity_winning aw2 where aw2.product_id= aw.product_id and aw2.activity_id=aw.activity_id) 'scanCount'," + // 扫码量
				"(select count(*) from qrcode_detail_"+companyId+" qr where qr.product_id=aw.product_id )'proQr'" +  //绑定数
				" from activity_winning aw " +
				" join product pro on pro.id=aw.product_id " +
				" left join activity ac on ac.id=aw.activity_id" +
				" left join prize pri on pri.id=aw.prize_id " +
				" where ac.company_id=?" );
		args = ArrayUtils.add(args, companyId);
		if(StringUtils.isNotBlank(activityId)){
			sqlWhere.append(" and ac.id = ?");
			args = ArrayUtils.add(args, activityId);
		}
		// 查询记录
		sqlQueryResult.append(sqlWhere).append(" group by aw.product_id, aw.activity_id,aw.prize_id");

		List<Map<String,Object>> result = jdbcTemplate.queryForList(sqlQueryResult.toString(), args);

		return result;
	}
   	 
}
